-- 用户表
CREATE TABLE [dbo].[Users] (
    [UserId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Username] NVARCHAR(50) NOT NULL UNIQUE,
    [Password] NVARCHAR(50) NOT NULL
);

create unique index uq_users_username on users (username)
go

-- 角色表
CREATE TABLE [dbo].[Roles] (
    [RoleId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [RoleName] NVARCHAR(50) NOT NULL UNIQUE,
    [RoleDescription] NVARCHAR(100) NULL
);

create unique index uq_roles_rolename on roles (rolename)
go

-- 用户角色映射表
CREATE TABLE [dbo].[UserRoleMapping] (
	[Id] int identity(1, 1) not null primary key,
    [UserId] INT NOT NULL,
    [RoleId] INT NOT NULL,
    CONSTRAINT [FK_UserRoleMapping_Users] FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users]([UserId]),
    CONSTRAINT [FK_UserRoleMapping_Roles] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[Roles]([RoleId])
);
go

create unique index uq_userrolemapping_userid_roleid on userrolemapping (userid, roleid)
go

-- 权限表
CREATE TABLE [dbo].[Permissions] (
    [PermissionId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [PermissionName] NVARCHAR(50) NOT NULL UNIQUE,
    [PermissionDescription] NVARCHAR(100) NULL
);
go

create unique index uq_permissions_permissionname on permissions (permissionname)
go

-- 角色权限映射表
CREATE TABLE [dbo].[RolePermissionMapping] (
	[Id] int identity(1, 1) not null primary key,
    [RoleId] INT NOT NULL,
    [PermissionId] INT NOT NULL,
    CONSTRAINT [FK_RolePermissionMapping_Roles] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[Roles]([RoleId]),
    CONSTRAINT [FK_RolePermissionMapping_Permissions] FOREIGN KEY ([PermissionId]) REFERENCES [dbo].[Permissions]([PermissionId])
);

create unique index uq_rolepermissionmapping_roleid_permissionid on rolepermissionmapping (roleid, permissionid)
go

-- 车辆品牌表
CREATE TABLE [dbo].[brand] (
    [id] INT NOT NULL PRIMARY KEY IDENTITY(1,1), -- 品牌ID
    [name] VARCHAR(50) NOT NULL, -- 品牌名称
    [country] VARCHAR(50) NOT NULL -- 品牌所属国家
)
go

create unique index uq_brand_name on brand (name)
go

-- 车辆信息表
CREATE TABLE [dbo].[vehicle] (
    [id] INT NOT NULL PRIMARY KEY IDENTITY(1,1), -- 车辆ID
    [brand_id] INT NOT NULL FOREIGN KEY REFERENCES [dbo].[brand]([id]), -- 品牌ID
    [model] VARCHAR(50) NOT NULL, -- 车型
    [color] VARCHAR(50) NOT NULL, -- 车身颜色
    [engine_number] VARCHAR(50) NOT NULL UNIQUE, -- 发动机号
    [vin] VARCHAR(50) NOT NULL UNIQUE, -- 车架号
    [production_date] DATE NOT NULL -- 生产日期
)
go

create unique index uq_vehicle_engine_number on vehicle (engine_number)
go

-- 车主信息表
CREATE TABLE [dbo].[owner] (
    [id] INT NOT NULL PRIMARY KEY IDENTITY(1,1), -- 车主ID
    [name] VARCHAR(50) NOT NULL, -- 车主姓名
    [id_card_number] VARCHAR(50) NOT NULL UNIQUE, -- 身份证号码
    [phone_number] VARCHAR(50) NOT NULL, -- 手机号码
    [address] VARCHAR(200) NOT NULL -- 住址
)
go

create unique index uq_owner_id_card_number on owner (id_card_number)
go

-- 车牌信息表
CREATE TABLE [dbo].[licenseplate] (
    [id] INT NOT NULL PRIMARY KEY IDENTITY(1,1), -- 车牌ID
    [license_number] VARCHAR(50) NOT NULL UNIQUE, -- 车牌号码
    [vehicle_id] INT NOT NULL FOREIGN KEY REFERENCES [dbo].[vehicle]([id]), -- 车辆ID
    [owner_id] INT NOT NULL FOREIGN KEY REFERENCES [dbo].[owner]([id]), -- 车主ID
    [issue_date] DATE NOT NULL, -- 发放日期
    [expire_date] DATE NOT NULL -- 过期日期
)
go

create unique index uq_licenseplate_license_number on licenseplate (license_number)
go

create unique index uq_licenseplate_vehicle_id on licenseplate (vehicle_id)
go